﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class relatorios_dr_r_visao_geral : System.Web.UI.Page
{
    protected double TotalToPay
    {
        get;
        set;
    }

    protected double TotalToReceive
    {
        get;
        set;
    }

    protected double TotalPayed
    {
        get;
        set;
    }

    protected double TotalDiscounts
    {
        get;
        set;
    }

    public int Entries
    {
        get;
        set;
    }

    public List<ReportDataList> AllReports
    {
        get;
        set;
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        List<string> accountsIds = new List<string>();
        accountsIds.Add("12"); // BB Aplicação
        accountsIds.Add("4"); // Ampliart
        accountsIds.Add("16"); // Ecos
        accountsIds.Add("14"); // Elfutec
        accountsIds.Add("7"); // Caixa Interno
        accountsIds.Add("2"); // Viana e CIA
        accountsIds.Add("5"); // Dr

        this.AllReports = new List<ReportDataList>();

        foreach (var id in accountsIds)
        {
            ReportDataList reports = new ReportDataList();
            ReportData report = null;
            string reportTitle = "";

            DateTime past = DateTime.Now.AddMonths(-6);
            for (int i = 0; i < 6; ++i)
            {
                // Mês re retrasado
                report = new ReportData();

                report.StartDate = String.Format("{0:dd/MM/yyyy}", past);
                past = past.AddMonths(1);
                past = past.AddDays(-1);

                report.EndDate = String.Format("{0:dd/MM/yyyy}", past);
                past = past.AddDays(1);

                report.OutcomingPair = this.Setup_Despesas(report.StartDate, report.EndDate, id);
                report.IncomingPair = this.Setup_Receitas(report.StartDate, report.EndDate, id);
                report.TotalOutcoming = this.CalcSum(report.OutcomingPair);
                report.TotalIncoming = this.CalcSum(report.IncomingPair);
                report.Title = "";
                if (report.TotalOutcoming > 0 && report.TotalIncoming > 0)
                {
                    // Getting the title
                    if (string.IsNullOrEmpty(reportTitle) == true)
                    {
                        foreach (var p in report.OutcomingPair)
                        {
                            if (string.IsNullOrEmpty(p.Key) == false)
                            {
                                reportTitle = p.Key;
                                break;
                            }
                        }
                        if (string.IsNullOrEmpty(reportTitle) == true)
                        {
                            foreach (var p in report.IncomingPair)
                            {
                                if (string.IsNullOrEmpty(p.Key) == false)
                                {
                                    reportTitle = p.Key;
                                    break;
                                }
                            }
                        }
                    }

                    report.Title = reportTitle;
                    reports.Reports.Add(report);
                }
            }           

            this.AllReports.Add(reports);
        }
    }

    public double CalcSum(List<StringPair> values)
    {
        double total = 0;

        foreach (var theValue in values)
        {
            try
            {
                total += double.Parse(theValue.Value);
            }
            catch { }
        }

        return total;
    }
    // Despesas
    public List<StringPair> Setup_Despesas(string startDate, string endDate, string accountId)
    {
        List<string> accountsIds = new List<string>();
        accountsIds.Add(accountId);

        List<StringPair> pair = new List<StringPair>();
        string provisionamento = "0";//this.Request["provisionamento"];
        string dataParaComparar = "Pagamento";
        if (provisionamento == "1")
        {
            dataParaComparar = "Vencimento";
        }

        string startDate2 = "";        
        DateTime previousDay = DateTime.Parse(startDate);
        startDate2 = previousDay.Day + "/" + previousDay.Month + "/" + previousDay.Year + " 00:00:00";

        string endDate2 = "";
        DateTime nextDay = DateTime.Parse(endDate);
        endDate2 = nextDay.Day + "/" + nextDay.Month + "/" + nextDay.Year + " 23:59:59";

        if (string.IsNullOrEmpty(startDate) == false
             || string.IsNullOrEmpty(endDate) == false
           )
        {
            PL.DataBase.DAO dao = new PL.DataBase.DAO();
            try
            {
                dao.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.ConpareOutcomingConnectionString);
                dao.Connector.Open();
                {                  
                    List<string> accounts = new List<string>();
                    string sql = "SELECT * FROM cpcontas_bancarias ";
                    sql += "WHERE ";
                    sql += " Excluido='False' ";

                    bool hasAccount = false;
                    string sqlAccounts = " AND (";
                    foreach (string id in accountsIds)
                    {
                        if (hasAccount == true)
                        {
                            sqlAccounts += " OR ";
                        }
                        hasAccount = true;
                        sqlAccounts += " Código='" + id + "' ";
                    }
                    sqlAccounts += " ) ";
                    if (hasAccount == true)
                    {
                        sql += sqlAccounts;
                    }

                    var reader = dao.Connector.QueryWithReader(sql);
                    while (reader.Read() == true)
                    {
                        accounts.Add("" + reader["Código"]);
                    }
                    reader.Close();

                    foreach (string conta in accounts)
                    {
                        sql = "SELECT SUM(derivedtbl_1.TotalPago) AS TotalPago, cpcontas_bancarias.Descrição, cpcontas_bancarias.Banco FROM (SELECT Cod_Pai, Credor, Fixa, CASE dbo.cpcontas_pagar.CodSis WHEN 1 THEN 'Ger.Com ®' WHEN 2 THEN 'SAC ®' WHEN 4 THEN 'MedTISS ®' WHEN 5 THEN 'Metra ®/ LabSys ®' ELSE 'ConPaRe ®' END AS Sistema, MAX([Nº da Parcela]) AS TotalParcelas, (SELECT SUM([Valor Parcela]) AS Expr1 FROM cpcontas_pagar AS parc WHERE (Credor = cpcontas_pagar.Credor) AND (Cod_Pai = cpcontas_pagar.Cod_Pai) AND (CodSis = cpcontas_pagar.CodSis) AND (Excluido = 0)) AS Total, (SELECT SUM(ValorPago) AS Expr1 FROM cpcontas_pagar AS parc WHERE (Credor = cpcontas_pagar.Credor) AND (Cod_Pai = cpcontas_pagar.Cod_Pai) AND (CodSis = cpcontas_pagar.CodSis) AND (Pagamento IS NOT NULL) AND (Excluido = 0) AND ([Conta Preferencial] = " + conta + ") AND (Pagamento >= '" + startDate2 + "') AND (Pagamento <= '" + endDate2 + "')) AS TotalPago, (SELECT SUM(Desconto) AS Expr1 FROM cpcontas_pagar AS parc WHERE (Credor = cpcontas_pagar.Credor) AND (Cod_Pai = cpcontas_pagar.Cod_Pai) AND (CodSis = cpcontas_pagar.CodSis) AND (Excluido = 0)) AS TotalDesconto, (SELECT MIN(Vencimento) AS Expr1 FROM cpcontas_pagar AS parc WHERE (Pagamento <= '01/01/1900 00:00:00' OR Pagamento IS NULL) AND (Cod_Pai = cpcontas_pagar.Cod_Pai) AND (Credor = cpcontas_pagar.Credor) AND (CodSis = cpcontas_pagar.CodSis) AND (Excluido = 0)) AS Vencimento, (SELECT MIN([Nº da Parcela]) AS Expr1 FROM cpcontas_pagar AS parc WHERE (Pagamento <= '01/01/1900 00:00:00' OR Pagamento IS NULL) AND (Credor = cpcontas_pagar.Credor) AND (Cod_Pai = cpcontas_pagar.Cod_Pai) AND (CodSis = cpcontas_pagar.CodSis) AND (Excluido = 0)) AS Parcela, CodSis FROM cpcontas_pagar WHERE (Excluido = 0) AND (Tipo >= - 1) AND ([Conta Preferencial] = " + conta + ") AND (Cod_Pai IN (SELECT Cod_Pai FROM cpcontas_pagar AS p WHERE (Excluido = 0) AND (Cod_Pai > 0) AND (Cod_Pai > 0) AND ([Conta Preferencial] = " + conta + ") AND (Pagamento >= '" + startDate2 + "') AND (Pagamento <= '" + endDate2 + "'))) GROUP BY Cod_Pai, Credor, Fixa, CodSis) AS derivedtbl_1 INNER JOIN cpcontas_bancarias ON cpcontas_bancarias.Código = " + conta + " GROUP BY cpcontas_bancarias.Descrição, cpcontas_bancarias.Banco ";
                        reader = dao.Connector.QueryWithReader(sql);
                        if (reader.Read() == true)
                        {
                            string payedValue = "";
                            payedValue = "" + reader["TotalPago"];
                            double dPayedValue = 0;
                            try
                            {
                                dPayedValue = double.Parse(payedValue);
                                this.TotalPayed += dPayedValue;
                            }
                            catch { }
                            pair.Add(new StringPair() { Key = "" + reader["Banco"], Value = "" + dPayedValue });                            
                        }
                        reader.Close();
                    }
                }
            }
            catch
            {
                this.Response.Write("   <span>" + "Ocorreu um erro no servidor!" + "</span>");    
            }
            dao.Connector.Close();
        }

        return pair;
    }

    // Receitas
    public List<StringPair> Setup_Receitas(string startDate, string endDate, string accountId)
    {
        List<string> accountsIds = new List<string>();
        accountsIds.Add(accountId);

        List<StringPair> pair = new List<StringPair>();        

        string provisionamento = this.Request["provisionamento"];
        string dataParaComparar = "Recebimento";
        if (provisionamento == "1")
        {
            dataParaComparar = "Vencimento";
        }

        string startDate2 = "";
        DateTime previousDay = DateTime.Parse(startDate);
        startDate2 = previousDay.Day + "/" + previousDay.Month + "/" + previousDay.Year + " 00:00:00";

        string endDate2 = "";        
        DateTime nextDay = DateTime.Parse(endDate);
        endDate2 = nextDay.Day + "/" + nextDay.Month + "/" + nextDay.Year + " 23:59:59";

        if (string.IsNullOrEmpty(startDate) == false
             || string.IsNullOrEmpty(endDate) == false
           )
        {
            PL.DataBase.DAO dao = new PL.DataBase.DAO();
            try
            {
                dao.Setup(PL.DataBase.DatabaseTypes.SqlServer, PL.General.Instance.ConpareIncomingConnectionString);
                dao.Connector.Open();

                {
                    List<string> accounts = new List<string>();
                    string sql = "SELECT * FROM cpcontas_bancarias ";
                    sql += "WHERE ";
                    sql += " Excluido='False' ";

                    bool hasAccount = false;
                    string sqlAccounts = " AND (";
                    foreach (string id in accountsIds)
                    {
                        if (hasAccount == true)
                        {
                            sqlAccounts += " OR ";
                        }
                        hasAccount = true;
                        sqlAccounts += " Código='" + id + "' ";
                    }
                    sqlAccounts += " ) ";
                    if (hasAccount == true)
                    {
                        sql += sqlAccounts;
                    }

                    var reader = dao.Connector.QueryWithReader(sql);
                    while (reader.Read() == true)
                    {
                        accounts.Add("" + reader["Código"]);
                    }
                    reader.Close();

                    foreach (string conta in accounts)
                    {                        
                        sql = "SELECT SUM(derivedtbl_1.TotalPago) AS TotalPago, cpcontas_bancarias.Descrição, cpcontas_bancarias.Banco FROM (SELECT TOP (100) PERCENT Cod_Pai, Devedor, CASE dbo.cpcontas_receber.CodSis WHEN 1 THEN 'Ger.Com ®' WHEN 2 THEN 'SAC ®' WHEN 4 THEN 'MedTISS ®' WHEN 5 THEN 'Metra ®/ LabSys ®' ELSE 'ConPaRe ®' END AS Sistema, (SELECT SUM([Valor Parcela]) AS Expr1 FROM cpcontas_receber AS parc WHERE (Cod_Pai = cpcontas_receber.Cod_Pai) AND (Devedor = cpcontas_receber.Devedor) AND (CodSis = cpcontas_receber.CodSis) AND (Excluido = 0)) AS Total, (SELECT SUM(ValorPago) AS Expr1 FROM cpcontas_receber AS parc WHERE (Cod_Pai = cpcontas_receber.Cod_Pai) AND (Devedor = cpcontas_receber.Devedor) AND (CodSis = cpcontas_receber.CodSis) AND (Recebimento IS NOT NULL) AND (Excluido = 0)) AS TotalPago, (SELECT SUM(Desconto) AS Expr1 FROM cpcontas_receber AS parc WHERE (Cod_Pai = cpcontas_receber.Cod_Pai) AND (Devedor = cpcontas_receber.Devedor) AND (CodSis = cpcontas_receber.CodSis) AND (Excluido = 0)) AS TotalDesconto, (SELECT MIN(Vencimento) AS Expr1 FROM cpcontas_receber AS parc WHERE (Cod_Pai = cpcontas_receber.Cod_Pai) AND (Recebimento <= '01/01/1900 00:00:00' OR Recebimento IS NULL) AND (Devedor = cpcontas_receber.Devedor) AND (CodSis = cpcontas_receber.CodSis) AND (Excluido = 0)) AS Vencimento, CodSis FROM cpcontas_receber WHERE (Excluido = 0) AND ([Conta Preferencial] = " + conta + ") AND (Tipo >= - 1) AND (Cod_Pai IN (SELECT Cod_Pai FROM cpcontas_receber AS r WHERE  (Cod_Pai > 0) AND (Cod_Pai > 0) AND ([Conta Preferencial] = " + conta + ")  AND (Recebimento >= '" + startDate2 + "') AND (Recebimento <= '" + endDate2 + "'))) GROUP BY Cod_Pai, Devedor, CodSis ORDER BY Vencimento) AS derivedtbl_1 INNER JOIN cpcontas_bancarias ON cpcontas_bancarias.Código = " + conta + " GROUP BY cpcontas_bancarias.Descrição, cpcontas_bancarias.Banco ";
                        reader = dao.Connector.QueryWithReader(sql);
                        while (reader.Read() == true)
                        {
                            string receivedValue = "";
                            double dReceivedValue = 0;
                            receivedValue = "" + reader["TotalPago"];
                            try
                            {
                                dReceivedValue = double.Parse(receivedValue);
                                this.TotalToReceive += dReceivedValue;
                            }
                            catch { }
                            pair.Add(new StringPair() { Key = "" + reader["Banco"], Value = "" + dReceivedValue });
                        }
                        reader.Close();
                    }
                }
            }
            catch
            {
                this.Response.Write("   <span>" + "Ocorreu um erro no servidor!" + "</span>");            
            }
            dao.Connector.Close();
        }

        return pair;
    }

    public double CalcPercentage(double total, double partial)
    {
        return ((partial * 100) / total) - 100;
    }
}